﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MRGSP.SurveyMaster.Core;
using MRGSP.SurveyMaster.Core.Model;
using MRGSP.SurveyMaster.Core.Repository;

namespace MRGSP.SurveyMaster.Data
{
    public class QuestionRepository : BaseRepository, IQuestionRepository
    {
        public QuestionRepository(IConnectionFactory connFactory)
            : base(connFactory)
        {
        }

        public long GetParentId(long id)
        {
            return Convert.ToInt64(DbUtil.ExecuteScalarSp("getParentId", Cs, new{id}));
        }

        public Question Get(long id)
        {
            using (var conn = new SqlConnection(Cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "getQuestion";
                    cmd.Parameters.Add("id", SqlDbType.BigInt).Value = id;
                    conn.Open();

                    using (var dr = cmd.ExecuteReader())
                    {
                        if (dr.Read())
                        {
                            var o = new Question();
                            o.Id = dr.GetInt64(0);
                            var val = dr.GetValue(1);
                            if (val != DBNull.Value)
                                o.ParentId = Convert.ToInt64(val);
                            o.Text = dr.GetString(2);
                            o.Type = (ControlType) dr.GetByte(3);
                            o.SurveyId = dr.GetInt64(4);
                            o.ColumnId = dr.GetInt64(5);
                            o.Mandatory = dr.GetBoolean(6);
                            return o;
                        }
                    }
                }
            }
            return null;
        }

        public int Delete(long id)
        {
            return DbUtil.ExecuteNonQuerySp("deleteQuestion", Cs, new {id});
        }

        public long Insert(long? parentId, string text, int typeId, long surveyId, bool isMandatory)
        {
            using (var conn = new SqlConnection(Cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "insertQuestion";
                    cmd.Parameters.Add("parentId", SqlDbType.BigInt).Value = parentId.ForNull(DBNull.Value);
                    cmd.Parameters.Add("text", SqlDbType.NVarChar, 200).Value = text;
                    cmd.Parameters.Add("typeId", SqlDbType.TinyInt).Value = typeId;
                    cmd.Parameters.Add("surveyId", SqlDbType.TinyInt).Value = surveyId;
                    cmd.Parameters.Add("mandatory", SqlDbType.Bit).Value = isMandatory;
                    conn.Open();

                    return Convert.ToInt64(cmd.ExecuteScalar());
                }
            }
        }

        public int InsertColumnQuestions(long id, string text, int typeId, bool mandatory)
        {
            using (var conn = new SqlConnection(Cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "insertColumnQuestions";
                    cmd.Parameters.Add("parentId", SqlDbType.BigInt).Value = id;
                    cmd.Parameters.Add("text", SqlDbType.NVarChar, 200).Value = text;
                    cmd.Parameters.Add("typeId", SqlDbType.TinyInt).Value = typeId;
                    cmd.Parameters.Add("mandatory", SqlDbType.Bit).Value = mandatory;
                    conn.Open();

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int InsertCellQuestions(long columnId, string text, int typeId)
        {
            using (var conn = new SqlConnection(Cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "insertCellQuestions";
                    cmd.Parameters.Add("columnId", SqlDbType.BigInt).Value = columnId;
                    cmd.Parameters.Add("text", SqlDbType.NVarChar, 200).Value = text;
                    cmd.Parameters.Add("typeId", SqlDbType.TinyInt).Value = typeId;
                    conn.Open();

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int Update(long id, string text, int typeId, bool isMandatory)
        {
            using (var conn = new SqlConnection(Cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "updateQuestion";
                    cmd.Parameters.Add("id", SqlDbType.BigInt).Value = id;
                    cmd.Parameters.Add("text", SqlDbType.NVarChar, 200).Value = text;
                    cmd.Parameters.Add("typeId", SqlDbType.TinyInt).Value = typeId;
                    cmd.Parameters.Add("mandatory", SqlDbType.Bit).Value = isMandatory;
                    conn.Open();

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public IEnumerable<Question> GetBySurveyId(long surveyId)
        {
            return DbUtil.ExecuteReaderSp<Question>("getQuestionsBySurveyId", Cs, new { surveyId });
        }

        public IEnumerable<Question> GetByQuestionId(long id)
        {
            return DbUtil.ExecuteReaderSp<Question>("getQuestionsByQuestionId", Cs, new {id});
        }

        public int DeleteByColumnId(long id)
        {
            return DbUtil.ExecuteNonQuerySp("deleteByColumnId", Cs, new {id});
        }

        public long GetGrandPaIdByColumnId(long columnId)
        {
            return Convert.ToInt64(DbUtil.ExecuteScalarSp("getGrandPaIdByColumnId", Cs, new {columnId}));
        }

        public long InsertRow(long parentId, int typeId, string text, long surveyId)
        {
            using (var conn = new SqlConnection(Cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "insertGridRow";
                    cmd.Parameters.Add("parentId", SqlDbType.BigInt).Value = parentId;
                    cmd.Parameters.Add("text", SqlDbType.NVarChar, 200).Value = text;
                    cmd.Parameters.Add("typeId", SqlDbType.TinyInt).Value = typeId;
                    cmd.Parameters.Add("surveyId", SqlDbType.TinyInt).Value = surveyId;
                    conn.Open();

                    return Convert.ToInt64(cmd.ExecuteScalar());
                }
            }
        }
    }
}